﻿Imports System.Data.OleDb


Public Class UserDAO

    Public Sub New()

    End Sub

    Public Function GetUserByUserName(ByVal _username As String) As DataSet
        Dim arrPara(0) As OleDbParameter

        'arrPara(0) = OleDbParameter("@Name", _username)
        Dim pr As New OleDbParameter("@Name", _username)
        arrPara(0) = pr
        Return objConnectionDAO.ExecuteStoreprocedure("spTDT_GetUser", arrPara)
    End Function

    Public Function GetPass(ByVal _username As String) As String
        Dim sql As String
        sql = "SELECT Password FROM UserAccounts WHERE Name='" & _username & "'"
        Return objConnectionDAO.ExecuteSelectQuery(sql).Tables(0).Rows(0).Item(0).ToString()
    End Function

    Public Function ChangePass(ByVal _username As String, ByVal _password As String) As Boolean
        Dim arrpara(1) As OleDbParameter
        Dim pa1 As New OleDbParameter("@UserName", _username)
        pa1.OleDbType = OleDbType.VarWChar
        Dim pa2 As New OleDbParameter("@Password", _password)
        pa2.OleDbType = OleDbType.VarWChar

        arrpara(0) = pa1
        arrpara(1) = pa2
        Return objConnectionDAO.ExecuteStoreprocedureNoneReturn("spTDT_ChangePassword", arrpara)
    End Function

    Public Function UserAccountsWithUserAccountID_Lookup() As DataSet
        Return objConnectionDAO.ExecuteStoreprocedure("spTDT_UserAccountsWithUserAccountID_Lookup", Nothing)
    End Function

    Public Function UserAccounts_Select(ByVal Username As String) As DataSet
        Dim arrpara(0) As OleDbParameter
        arrpara(0) = New OleDbParameter("@Username", Username)
        arrpara(0).OleDbType = OleDbType.VarWChar
        Return objConnectionDAO.ExecuteStoreprocedure("spTDT_UserAccounts_Select", arrpara)
    End Function

    Public Function UserAccounts_Insert(ByVal Name As String, _
                                        ByVal FullName As String, _
                                        ByVal Description As String, _
                                        ByVal Office As String, _
                                        ByVal PhoneNumber As String, _
                                        ByVal EmailAddress As String, _
                                        ByVal Password As String, _
                                        ByVal PasswordChgAtLogonFlag As Boolean, _
                                        ByVal CannotChgPasswordFlag As Boolean, _
                                        ByVal StatusFlag As Boolean, _
                                        ByVal CashBookFlag As Boolean, _
                                        ByVal ModifiedOn As Date, _
                                        ByVal ModifiedBy As String, _
                                        ByVal CreatedOn As Date, _
                                        ByVal CreatedBy As String) As DataSet
        Dim arrpara(14) As OleDbParameter
        arrpara(0) = New OleDbParameter("@Name", Name)
        arrpara(0).OleDbType = OleDbType.VarWChar

        arrpara(1) = New OleDbParameter("@FullName", FullName)
        arrpara(1).OleDbType = OleDbType.VarWChar

        arrpara(2) = New OleDbParameter("@Description", Description)
        arrpara(2).OleDbType = OleDbType.VarWChar

        arrpara(3) = New OleDbParameter("@Office", Office)
        arrpara(3).OleDbType = OleDbType.VarWChar

        arrpara(4) = New OleDbParameter("@PhoneNumber", PhoneNumber)
        arrpara(4).OleDbType = OleDbType.VarWChar

        arrpara(5) = New OleDbParameter("@EmailAddress", EmailAddress)
        arrpara(5).OleDbType = OleDbType.VarWChar

        arrpara(6) = New OleDbParameter("@Password", Password)
        arrpara(6).OleDbType = OleDbType.VarWChar

        arrpara(7) = New OleDbParameter("@PasswordChgAtLogonFlag", PasswordChgAtLogonFlag)
        arrpara(7).OleDbType = OleDbType.Boolean

        arrpara(8) = New OleDbParameter("@CannotChgPasswordFlag", CannotChgPasswordFlag)
        arrpara(8).OleDbType = OleDbType.Boolean

        arrpara(9) = New OleDbParameter("@StatusFlag", StatusFlag)
        arrpara(9).OleDbType = OleDbType.Boolean

        arrpara(10) = New OleDbParameter("@CashBookFlag", CashBookFlag)
        arrpara(10).OleDbType = OleDbType.Boolean

        arrpara(11) = New OleDbParameter("@ModifiedOn", ModifiedOn)
        arrpara(11).OleDbType = OleDbType.Date

        arrpara(12) = New OleDbParameter("@ModifiedBy", ModifiedBy)
        arrpara(12).OleDbType = OleDbType.VarWChar

        arrpara(13) = New OleDbParameter("@CreatedOn", CreatedOn)
        arrpara(13).OleDbType = OleDbType.Date

        arrpara(14) = New OleDbParameter("@CreatedBy", CreatedBy)
        arrpara(14).OleDbType = OleDbType.VarWChar
        Return objConnectionDAO.ExecuteStoreprocedure("spTDT_UserAccounts_Select", arrpara)
    End Function

    Public Function UserAccounts_Update_Email(ByVal UserAccountID As Integer, _
                                              ByVal Email As String) As Boolean
        Dim arrpara(1) As OleDbParameter
        arrpara(0) = New OleDbParameter("@UserAccountID", UserAccountID)
        arrpara(0).OleDbType = OleDbType.Integer

        arrpara(1) = New OleDbParameter("@Email", Email)
        arrpara(1).OleDbType = OleDbType.VarWChar

        Return objConnectionDAO.ExecuteStoreprocedureNoneReturn("spTDT_UserAccounts_Update_Email", arrpara)
    End Function

    Public Function UserAccounts_Update_Name(ByVal UserAccountID As Integer, _
                                              ByVal Name As String) As Boolean
        Dim arrpara(1) As OleDbParameter
        arrpara(0) = New OleDbParameter("@UserAccountID", UserAccountID)
        arrpara(0).OleDbType = OleDbType.Integer

        arrpara(1) = New OleDbParameter("@Name", Name)
        arrpara(1).OleDbType = OleDbType.VarWChar

        Return objConnectionDAO.ExecuteStoreprocedureNoneReturn("spTDT_UserAccounts_Update_Name", arrpara)
    End Function

    Public Function UserAccounts_Update_FullName(ByVal UserAccountID As Integer, _
                                              ByVal FullName As String) As Boolean
        Dim arrpara(1) As OleDbParameter
        arrpara(0) = New OleDbParameter("@UserAccountID", UserAccountID)
        arrpara(0).OleDbType = OleDbType.Integer

        arrpara(1) = New OleDbParameter("@FullName", FullName)
        arrpara(1).OleDbType = OleDbType.VarWChar

        Return objConnectionDAO.ExecuteStoreprocedureNoneReturn("spTDT_UserAccounts_Update_FullName", arrpara)
    End Function

    Public Function UserAccounts_Update_PhoneNumber(ByVal UserAccountID As Integer, _
                                                    ByVal PhoneNumber As String) As Boolean
        Dim arrpara(1) As OleDbParameter
        arrpara(0) = New OleDbParameter("@UserAccountID", UserAccountID)
        arrpara(0).OleDbType = OleDbType.Integer

        arrpara(1) = New OleDbParameter("@PhoneNumber", PhoneNumber)
        arrpara(1).OleDbType = OleDbType.VarWChar

        Return objConnectionDAO.ExecuteStoreprocedureNoneReturn("spTDT_UserAccounts_Update_PhoneNumber", arrpara)
    End Function


    Public Function UserAccounts_Update(ByVal UserAccountID As Integer, _
                                        ByVal Name As String, _
                                        ByVal FullName As String, _
                                        ByVal Description As String, _
                                        ByVal Office As String, _
                                        ByVal PhoneNumber As String, _
                                        ByVal EmailAddress As String, _
                                        ByVal Password As String, _
                                        ByVal PasswordChgAtLogonFlag As Boolean, _
                                        ByVal CannotChgPasswordFlag As Boolean, _
                                        ByVal StatusFlag As Boolean, _
                                        ByVal CashBookFlag As Boolean, _
                                        ByVal ModifiedOn As Date, _
                                        ByVal ModifiedBy As String, _
                                        ByVal CreatedOn As Date, _
                                        ByVal CreatedBy As String) As Boolean
        Dim arrpara(15) As OleDbParameter
        arrpara(0) = New OleDbParameter("@Name", Name)
        arrpara(0).OleDbType = OleDbType.VarWChar

        arrpara(1) = New OleDbParameter("@FullName", FullName)
        arrpara(1).OleDbType = OleDbType.VarWChar

        arrpara(2) = New OleDbParameter("@Description", Description)
        arrpara(2).OleDbType = OleDbType.VarWChar

        arrpara(3) = New OleDbParameter("@Office", Office)
        arrpara(3).OleDbType = OleDbType.VarWChar

        arrpara(4) = New OleDbParameter("@PhoneNumber", PhoneNumber)
        arrpara(4).OleDbType = OleDbType.VarWChar

        arrpara(5) = New OleDbParameter("@EmailAddress", EmailAddress)
        arrpara(5).OleDbType = OleDbType.VarWChar

        arrpara(6) = New OleDbParameter("@Password", Password)
        arrpara(6).OleDbType = OleDbType.VarWChar

        arrpara(7) = New OleDbParameter("@PasswordChgAtLogonFlag", PasswordChgAtLogonFlag)
        arrpara(7).OleDbType = OleDbType.Boolean

        arrpara(8) = New OleDbParameter("@CannotChgPasswordFlag", CannotChgPasswordFlag)
        arrpara(8).OleDbType = OleDbType.Boolean

        arrpara(9) = New OleDbParameter("@StatusFlag", StatusFlag)
        arrpara(9).OleDbType = OleDbType.Boolean

        arrpara(10) = New OleDbParameter("@CashBookFlag", CashBookFlag)
        arrpara(10).OleDbType = OleDbType.Boolean

        arrpara(11) = New OleDbParameter("@ModifiedOn", ModifiedOn)
        arrpara(11).OleDbType = OleDbType.Date

        arrpara(12) = New OleDbParameter("@ModifiedBy", ModifiedBy)
        arrpara(12).OleDbType = OleDbType.VarWChar

        arrpara(13) = New OleDbParameter("@CreatedOn", CreatedOn)
        arrpara(13).OleDbType = OleDbType.Date

        arrpara(14) = New OleDbParameter("@CreatedBy", CreatedBy)
        arrpara(14).OleDbType = OleDbType.VarWChar

        arrpara(15) = New OleDbParameter("@UserAccountID", UserAccountID)
        arrpara(15).OleDbType = OleDbType.Integer

        Return objConnectionDAO.ExecuteStoreprocedureNoneReturn("spTDT_UserAccounts_Update", arrpara)
    End Function


    Public Function UserAccounts_Delete(ByVal UserAccountID As Integer) As Boolean
        Dim arrpara(1) As OleDbParameter
        arrpara(0) = New OleDbParameter("@UserAccountID", UserAccountID)
        arrpara(0).OleDbType = OleDbType.VarWChar

        arrpara(1) = New OleDbParameter("@Success", OleDbType.Boolean)
        arrpara(1).Direction = ParameterDirection.Output

        objConnectionDAO.ExecuteStoreprocedureNoneReturn("spTDT_UserAccounts_Delete", arrpara)
        Return arrpara(1).Value
    End Function

    Public Function UserAccounts_Check(ByVal Username As String) As Boolean
        Dim para(1) As OleDbParameter
        para(0) = New OleDbParameter("@Username", Username)
        para(0).OleDbType = OleDbType.VarWChar

        para(1) = New OleDbParameter("@Return_Value", OleDbType.Boolean)
        para(1).Direction = ParameterDirection.Output

        objConnectionDAO.ExecuteStoreprocedureNoneReturn("spTDT_UserAccounts_Check", para)
        Return para(1).Value
    End Function

    Public Function UserAccounts_Lookup() As DataSet
        Return objConnectionDAO.ExecuteStoreprocedure("spTDT_UserAccounts_Lookup", Nothing)
    End Function

    Public Function UserAccounts_List() As DataSet
        Return objConnectionDAO.ExecuteStoreprocedure("spTDT_UserAccounts_List", Nothing)
    End Function

End Class
